set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_dm.dm_express_analyse_daily_dt
select
    scan_date
     ,network_code
     ,network_name
     ,max(nvl(send_cnt,0))
     ,max(nvl(arrival_cnt,0))
     ,max(nvl(loading_network_cnt,0))
     ,max(nvl(loading_nextstation_cnt,0))
     ,dt
from (
     select
         to_date(scan_time)  as scan_date
          ,scan_site_code     as network_code
          ,scan_site          as network_name
          ,count(waybill_no)  as send_cnt
          ,null               as arrival_cnt
          ,null               as loading_network_cnt
          ,null               as loading_nextstation_cnt
          ,to_date(scan_time) as dt
     from(
             select
                 waybill_no
                  ,scan_time
                  ,input_site as scan_site
                  ,scan_site_code
                  ,row_number() over(partition by waybill_no,scan_site_code order by scan_time desc) as rn
             from jms_dwd.dwd_tab_barscan_send_base_dt
             where dt between date_sub('{{ execution_date | cst_ds }}',7) and  '{{ execution_date | cst_ds }}'
               and to_date(scan_time) between date_sub('{{ execution_date | cst_ds }}',7) and  '{{ execution_date | cst_ds }}'
         ) a where a.rn = 1
     group by  to_date(scan_time)
            ,scan_site_code
            ,scan_site
     union all
     select
         to_date(scan_time) as scan_date
          ,scan_site_code     as network_code
          ,scan_site          as network_name
          ,null               as send_cnt
          ,count(waybill_no)  as arrival_cnt
          ,null               as loading_network_cnt
          ,null               as loading_nextstation_cnt
          ,to_date(scan_time) as dt
     from(
             select
                 waybill_no
                  ,scan_time
                  ,scan_site_code
                  ,scan_site
                  ,row_number() over(partition by waybill_no,scan_site_code order by scan_time desc) as rn
             from jms_dwd.dwd_tab_barscan_arrival_base_dt
             where dt between date_sub('{{ execution_date | cst_ds }}',7) and  '{{ execution_date | cst_ds }}'
               and to_date(scan_time) between date_sub('{{ execution_date | cst_ds }}',7) and  '{{ execution_date | cst_ds }}'
         ) a where a.rn = 1
     group by  to_date(scan_time)
            ,scan_site_code
            ,scan_site
     union all
     select
         to_date(scan_time) as scan_date
          ,scan_site_code     as network_code
          ,scan_site          as network_name
          ,null               as send_cnt
          ,null               as arrival_cnt
          ,count(waybill_no)  as loading_network_cnt
          ,null               as loading_nextstation_cnt
          ,to_date(scan_time) as dt
     from(
             select
                 waybill_no
                  ,scan_time
                  ,scan_site_code
                  ,scan_site
                  ,row_number() over(partition by waybill_no,scan_site_code order by scan_time desc) as rn
             from jms_dwd.dwd_tab_barscan_loading_base_dt
             where dt between date_sub('{{ execution_date | cst_ds }}',7) and  '{{ execution_date | cst_ds }}'
               and to_date(scan_time) between date_sub('{{ execution_date | cst_ds }}',7) and  '{{ execution_date | cst_ds }}'
         ) a where a.rn = 1
     group by  to_date(scan_time)
            ,scan_site_code
            ,scan_site
     union all
     select
         to_date(scan_time) as scan_date
          ,next_station_code  as network_code
          ,next_station       as network_name
          ,null               as send_cnt
          ,null               as arrival_cnt
          ,null               as loading_network_cnt
          ,count(waybill_no)  as loading_nextstation_cnt
          ,to_date(scan_time) as dt
     from(
             select
                 waybill_no
                  ,scan_time
                  ,next_station_code
                  ,next_station
                  ,row_number() over(partition by waybill_no,next_station_code order by scan_time desc) as rn
             from jms_dwd.dwd_tab_barscan_loading_base_dt
             where dt between date_sub('{{ execution_date | cst_ds }}',7) and  '{{ execution_date | cst_ds }}'
               and to_date(scan_time) between date_sub('{{ execution_date | cst_ds }}',7) and  '{{ execution_date | cst_ds }}'
         ) a where a.rn = 1
     group by  to_date(scan_time)
            ,next_station_code
            ,next_station
 ) a group by scan_date
            ,network_code
            ,network_name
            ,dt
distribute by dt,pmod(hash(rand()),1);